-- 创建数据库
use master
if exists(select * from sysdatabases where name='KTV')
drop database KTV
go
create database KTV
go
use KTV

-- 创建歌曲类型表
create table SongType
(
    Id int  primary key,
    TypeName varchar(32) not null unique
)
go
insert into  SongType
select 1,'流行' union
select 2,'经典' union
select 3,'影视' union
select 4,'游戏' union
select 5,'儿童' union
select 6,'京剧' 
go

--创建歌手类型
create table SingerType
(
    Id int  primary key,
    TypeName varchar(32) not null unique
)
insert into  SingerType
select 1,'日韩' union
select 2,'大陆' union
select 3,'港台' union
select 4,'欧美' union
select 5,'其他'
go

---歌手表
create table Singer
(
  Id int primary key ,
  Name varchar(32) unique not null,
  Gender varchar(2) not null,
  SingerTypeId int references SingerType(Id) ,
  PhotoUrl varchar(128) not null,
  Remark varchar(32) not null
)
insert into Singer
select 1,'周杰伦','男',3,'d:\ktv\zjl.jpg','周周' union
select 2,'梁静茹','女',5,'d:\ktv\ljr.jpg','梁静茹' union
select 3,'王菲','女',3,'d:\ktv\wf.jpg','王菲' 

go

--歌曲表
create table Song
(
  Id int primary key,
  Name varchar(32) not null unique,
  PinYin varchar(32) not null,
  SongTypeId int references SongType(Id) not null,
  SingerId int references Singer(Id) not null,
  PlayUrl varchar(128) not null,
  PlayCount int not null
)
insert into Song
select 1,'千里之外','qlzw',1,1,'d:\ktv\qlzw.mp4',0 union
select 2,'可惜不是你','kxbsn',1,2,'d:\ktv\kxbsn.mp4',0 union
select 3,'爱笑的天使','axdts',1,3,'d:\ktv\axdts.mp4',0 union
select 4,'听妈妈的话','tmmdh',1,1,'d:\ktv\tmmdh.mp4',0 union
select 5,'匆匆那年','ccnan',1,3,'d:\ktv\ccnan.mp4',0 

--管理员表
 create table  Manager
 (
    Id int primary key,
    UserName varchar(32) not null unique ,
    Password varchar(32) not null
 )
 insert into Manager
 select 1,'admin','admin'




